package com.wang.springbootinit.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.wang.springbootinit.common.ErrorCode;
import com.wang.springbootinit.exception.BusinessException;
import com.wang.springbootinit.mapper.GradeRepository;
import com.wang.springbootinit.model.entity.Grade;
import com.wang.springbootinit.model.entity.Student;
import com.wang.springbootinit.model.entity.User;
import com.wang.springbootinit.model.param.GradeParam;
import com.wang.springbootinit.model.param.GradeUpdateParam;
import com.wang.springbootinit.model.vo.GradeStudentVO;
import com.wang.springbootinit.service.GradeService;
import com.wang.springbootinit.service.StudentService;
import com.wang.springbootinit.service.UserService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import static java.util.stream.Collectors.toMap;


@Service
@Transactional
public class GradeServiceImpl extends ServiceImpl<GradeRepository, Grade> implements GradeService {

    @Autowired
    private GradeRepository gradeRepository;

    @Autowired
    private StudentService studentService;

    @Resource
    private UserService userService;

    @Override
    public Boolean saveGrade(GradeParam param) {
        String studentNum = param.getStudentNum();
        if(StrUtil.isEmpty(studentNum)){
            throw new BusinessException(ErrorCode.PARAMS_ERROR);
        }
        Student student = studentService.getStudentByNumber(studentNum);
        if(ObjectUtil.isNull(student)){
            throw new BusinessException(ErrorCode.STUDENT_CODE_NOT_EXIST);
        }
        Grade grade = new Grade();
        BeanUtils.copyProperties(param,grade);
        grade.setStudentId(student.getId());
        gradeRepository.insert(grade);
        return true;
    }

    @Override
    public Grade updateGrade(Long id, Grade grade) {
        Grade existingGrade = gradeRepository.selectById(id);
        if(ObjectUtil.isNull(existingGrade)){
            throw new RuntimeException("成绩不存在");
        }

        existingGrade.setScore(grade.getScore());
        existingGrade.setUpdateTime(LocalDateTime.now());
        existingGrade.setUpdateBy(grade.getUpdateBy());
        
        gradeRepository.insert(existingGrade);
        return existingGrade;
    }

    @Override
    public void deleteGrade(Long id) {
        gradeRepository.deleteById(id);
    }

    @Override
    public List<Grade> getGradeByStudentId(Long studentId) {
        LambdaQueryWrapper<Grade> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Grade::getStudentId,studentId);
        List<Grade> list = list(queryWrapper);
        if(CollUtil.isEmpty(list)){
            return new ArrayList<Grade>();
        }
        return list;
    }

    @Override
    public List<Grade> getStudentGrades(Long studentId) {
        return gradeRepository.findByStudentId(studentId);
    }

    @Override
    public List<Grade> getStudentSemesterGrades(Long studentId, String semester) {
        return gradeRepository.findByStudentIdAndSemester(studentId, semester);
    }

    @Override
    public List<Grade> getCourseGrades(String courseName) {
        return gradeRepository.findByCourseName(courseName);
    }

    @Override
    public int importGradesFromExcel(MultipartFile file) throws Exception {
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        
        List<Grade> grades = new ArrayList<>();
        int rowNum = 1; // 跳过表头
        
        while (rowNum <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowNum++);
            if (row == null) continue;
            
            String studentNumber = getStringCellValue(row.getCell(0));
            String courseName = getStringCellValue(row.getCell(1));
            Double score = getNumericCellValue(row.getCell(2));
            String semester = getStringCellValue(row.getCell(3));
            String examType = getStringCellValue(row.getCell(4));
            
            Student student = studentService.getStudentById(Long.valueOf(studentNumber));
            
            Grade grade = new Grade();
            grade.setStudentId(student.getId());
            grade.setCourseName(courseName);
            grade.setScore(score);
            grade.setSemester(semester);
            grade.setExamType(examType);
            grade.setCreateTime(LocalDateTime.now());
            grade.setUpdateTime(LocalDateTime.now());
            grade.setCreateBy("System");
            
            grades.add(grade);
        }

        for (Grade grade : grades) {
            gradeRepository.insert(grade);

        }
        return grades.size();
    }

    @Override
    public byte[] exportGradesToExcel(Long studentId, String semester, String courseName) {
        List<Grade> grades;
        if (studentId != null && semester != null) {
            grades = gradeRepository.findByStudentIdAndSemester(studentId, semester);
        } else if (studentId != null) {
            grades = gradeRepository.findByStudentId(studentId);
        } else if (courseName != null) {
            grades = gradeRepository.findByCourseName(courseName);
        } else {
            grades = list();
        }

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("成绩单");
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("学号");
            headerRow.createCell(1).setCellValue("姓名");
            headerRow.createCell(2).setCellValue("课程");
            headerRow.createCell(3).setCellValue("分数");
            headerRow.createCell(4).setCellValue("学���");
            headerRow.createCell(5).setCellValue("考试类型");


            // 填充数据
            int rowNum = 1;
            for (Grade grade : grades) {
                Row row = sheet.createRow(rowNum++);
                Student student = studentService.getStudentById(grade.getStudentId());
                row.createCell(0).setCellValue(student.getStudentNumber());
                row.createCell(1).setCellValue(student.getName());
                row.createCell(2).setCellValue(grade.getCourseName());
                row.createCell(3).setCellValue(grade.getScore());
                row.createCell(4).setCellValue(grade.getSemester());
                row.createCell(5).setCellValue(grade.getExamType());
            }
            
            // 自动调整列宽
            for (int i = 0; i < 6; i++) {
                sheet.autoSizeColumn(i);
            }
            
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
            
        } catch (IOException e) {
            throw new RuntimeException("导出成绩单失败", e);
        }
    }

    @Override
    public Page<GradeStudentVO> listGrades(String className, String courseName, String semester, int page, int size) {
        // 创建MyBatis-Plus的分页对象
        Page<Grade> pageParam = new Page<>(page, size);
        
        // 构建查询条件
        LambdaQueryWrapper<Grade> queryWrapper = new LambdaQueryWrapper<>();
        
        // 添加学生ID过滤条件
        if (StrUtil.isNotEmpty(className)) {
            queryWrapper.eq(Grade::getClassName, className);
        }
        
        // 添加课程名称过滤条件
        if (StrUtil.isNotEmpty(courseName)) {
            queryWrapper.like(Grade::getCourseName, courseName);
        }
        
        // 添加学期过滤条件
        if (StrUtil.isNotEmpty(semester)) {
            queryWrapper.eq(Grade::getSemester, semester);
        }

        Page<Grade> result = page(pageParam, queryWrapper);
        List<Grade> records = result.getRecords();
        if(CollUtil.isEmpty(records)){
            return new Page<>();
        }
        List<Long> studentIds = records.stream().map(Grade::getStudentId).collect(Collectors.toList());
        if(CollUtil.isEmpty(studentIds)){
            throw new BusinessException(ErrorCode.NOT_FOUND_ERROR);
        }
        List<Student> students = studentService.listByIds(studentIds);
        Map<Long, Student> studentMap = students.stream().collect(toMap(Student::getId, Function.identity(), (k1, k2) -> k1));

        List<GradeStudentVO> voList = result.getRecords().stream().map(item -> {
            GradeStudentVO gradeStudentVO = new GradeStudentVO();
            BeanUtils.copyProperties(item, gradeStudentVO);
            Student student = studentMap.get(item.getStudentId());
            if (ObjectUtil.isNotNull(student)) {
                gradeStudentVO.setStudentNumber(student.getStudentNumber());
                gradeStudentVO.setStudentName(student.getName());
            }
            return gradeStudentVO;
        }).collect(Collectors.toList());

        Page<GradeStudentVO> resPage = new Page<>();
        resPage.setTotal(result.getTotal());
        resPage.setCurrent(resPage.getCurrent());
        resPage.setPages(resPage.getPages());
        resPage.setSize(resPage.getSize());
        resPage.setRecords(voList);


        // 执行查询并返回分页结果
        return resPage;
    }

    @Override
    public Map<String, List<String>> getOptions() {
        LambdaQueryWrapper<Grade> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(Grade::getSemester);
        queryWrapper.groupBy(Grade::getSemester);
        List<Grade> list = list(queryWrapper);
        List<String> semesterOptions = list.stream().map(Grade::getSemester).collect(Collectors.toList());

        LambdaQueryWrapper<Grade> queryWrapper2 = new LambdaQueryWrapper<>();
        queryWrapper2.select(Grade::getClassName);
        queryWrapper2.groupBy(Grade::getClassName);
        List<Grade> list2 = list(queryWrapper2);
        List<String> classNameOptions = list2.stream().map(Grade::getClassName).collect(Collectors.toList());

        LambdaQueryWrapper<Grade> queryWrapper23 = new LambdaQueryWrapper<>();
        queryWrapper23.select(Grade::getCourseName);
        queryWrapper23.groupBy(Grade::getCourseName);
        List<Grade> list3 = list(queryWrapper23);
        List<String> courseNameOptions = list3.stream().map(Grade::getCourseName).collect(Collectors.toList());

        Map<String, List<String>> res = new HashMap<>();
        res.put("semesterOptions",semesterOptions);
        res.put("classNameOptions",classNameOptions);
        res.put("courseNameOptions",courseNameOptions);
        return res;
    }

    @Override
    public Boolean updateBatch(GradeUpdateParam param) {
        List<Grade> gradeList = param.getGradeList();
        if(CollUtil.isEmpty(gradeList)){
            throw new BusinessException(ErrorCode.PARAMS_ERROR);
        }
        List<Grade> list = param.getGradeList();
        updateBatchById(list);
        return true;
    }

    /**
     * 获取当前学生的配置信息
     * @return
     */
    @Override
    public Map<String, List<String>> getOptionsByCurrentUser(HttpServletRequest request) {
        User loginUser = userService.getLoginUser(request);
        Long studentId = loginUser.getStudentId();

        if(ObjectUtil.isNull(studentId)){
            throw new BusinessException(ErrorCode.ACCOUNT_DOES_NOT_EXIST);
        }
        Student student = studentService.getStudentById(studentId);



        LambdaQueryWrapper<Grade> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(Grade::getSemester);
        queryWrapper.eq(Grade::getStudentId,student.getId());
        queryWrapper.groupBy(Grade::getSemester);
        List<Grade> list = list(queryWrapper);
        List<String> semesterOptions = list.stream().map(Grade::getSemester).collect(Collectors.toList());

        LambdaQueryWrapper<Grade> queryWrapper2 = new LambdaQueryWrapper<>();
        queryWrapper2.select(Grade::getClassName);
        queryWrapper2.eq(Grade::getStudentId,student.getId());
        queryWrapper2.groupBy(Grade::getClassName);
        List<Grade> list2 = list(queryWrapper2);
        List<String> classNameOptions = list2.stream().map(Grade::getClassName).collect(Collectors.toList());

        LambdaQueryWrapper<Grade> queryWrapper23 = new LambdaQueryWrapper<>();
        queryWrapper23.select(Grade::getCourseName);
        queryWrapper23.eq(Grade::getStudentId,student.getId());
        queryWrapper23.groupBy(Grade::getCourseName);
        List<Grade> list3 = list(queryWrapper23);
        List<String> courseNameOptions = list3.stream().map(Grade::getCourseName).collect(Collectors.toList());

        Map<String, List<String>> res = new HashMap<>();
        res.put("semesterOptions",semesterOptions);
        res.put("classNameOptions",classNameOptions);
        res.put("courseNameOptions",courseNameOptions);
        return res;
    }

    @Override
    public List<Grade> getCurrentStudentGrade(HttpServletRequest request) {
        User loginUser = userService.getLoginUser(request);
        Long studentId = loginUser.getStudentId();

        if(ObjectUtil.isNull(studentId)){
            throw new BusinessException(ErrorCode.ACCOUNT_DOES_NOT_EXIST);
        }
        Student student = studentService.getStudentById(studentId);
        LambdaQueryWrapper<Grade> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Grade::getStudentId,student.getId());
        List<Grade> list = list(queryWrapper);
        if(CollUtil.isEmpty(list)){
            return new ArrayList<>();
        }
        return list;
    }

    private String getStringCellValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    private Double getNumericCellValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(CellType.NUMERIC);
        return cell.getNumericCellValue();
    }
} 